﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBAccess;
using SSPharma.Common;
using SSPharma.Model;
using System.Data.Common;
using SSPharma.Biz.Common;
using System.Transactions;

namespace SSPharma.Biz.DanhMuc
{
    class BizDanhMucQuocTich
    {
        //khai báo hàm Common dùng để lấy các hàm dùng chung
        private BizCommon _BizCommon = new BizCommon();
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// lấy quốc tịch theo mã quốc tịch
        /// </summary>
        /// <param name="Ma"></param>
        /// <returns></returns>
        public DataTable ft_GetDataDanhMucQuocTichTheoMa(string Ma)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT * from HT_DM_QuocTich where MaQuocTich = @Ma");
            SQLServer.Param[] Params = new SQLServer.Param[1];
            Params[0] = new SQLServer.Param("@Ma", Ma);
            return Global.DataFactory.GetDataTable(strSQL.ToString(), Params);
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Lay cau truc de luu du lieu xong database
        /// </summary>
        /// <returns></returns>
        public DataSet fs_LayCauTrucTable()
        {
            DataSet dsdata = new DataSet();
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT a.* from HT_DM_QuocTich a where 1=2");
            DataTable dt_QuocTich = Global.DataFactory.GetDataTable(strSQL.ToString());
            dt_QuocTich.TableName = "dt_QuocTich";
            dsdata.Tables.Add(dt_QuocTich);
            return dsdata;
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// kiem tra giua counter cu va counter moi
        /// </summary>
        /// <param name="p_sCounterOld"></param>
        /// <param name="p_sTienTo"></param>
        /// <returns></returns>
        public bool fb_KiemTraSoCounter(string p_sCounterOld, string p_sTienTo)
        {
            string p_sCounterNew = _BizCommon.fs_GetCodeId(p_sTienTo);
            if (p_sCounterNew.Equals(p_sCounterOld))
                return false;
            return true;
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Them danh muc quốc tịch.
        /// </summary>
        /// <param name="p_dtQuocTich"></param>
        /// <param name="TienTo"></param>
        public void fg_InsertDanhMucQuocTich(DataTable p_dtQuocTich, string TienTo)
        {
            try
            {
                string query_quoctich = "Select * from HT_DM_QuocTich where 1 = 2";
                string query_congto = "Select * from HT_CongToHienHanh_" + TienTo + " where 1 = 2";
                if (p_dtQuocTich.Rows[0].RowState == DataRowState.Added)
                {
                    DataTable p_dtCongTo = _BizCommon.ft_GetDataCongToHienHanh(TienTo);
                    // TH01: lỗi
                    if (p_dtCongTo == null)
                    {
                        throw new System.Configuration.Provider.ProviderException("Không lấy được số chứng từ");
                    }
                    // TH01: tạo counter lưu xuống database
                    else if (p_dtCongTo.Rows.Count > 0)
                    {
                        using (TransactionScope scope = new TransactionScope())
                        {
                            string p_sCreateCounter = _BizCommon.fs_GetCodeId(TienTo);
                            p_dtQuocTich.Rows[0]["MaQuocTich"] = p_sCreateCounter;
                            p_dtCongTo.Rows[0]["SoHienTai"] = !string.IsNullOrEmpty(p_dtCongTo.Rows[0]["SoHienTai"].ToString()) ? (Convert.ToInt64(p_dtCongTo.Rows[0]["SoHienTai"]) + 1) : 1;
                            Global.DataFactory.RunSaveData(p_dtCongTo, query_congto);
                            Global.DataFactory.RunSaveData(p_dtQuocTich, query_quoctich);
                            scope.Complete();
                            scope.Dispose();
                        }
                    }
                    // TH03: chưa có counter thì tạo counter đầu tiên
                    else if (p_dtCongTo.Rows.Count == 0)
                    {
                        // chạy hàm tạo counter đầu tiên.
                        string createCounter = _BizCommon.fs_CreateCodeId(TienTo);
                        p_dtCongTo = _BizCommon.ft_GetDataCongToHienHanh(TienTo);
                        using (TransactionScope scope = new TransactionScope())
                        {
                            p_dtQuocTich.Rows[0]["MaQuocTich"] = createCounter;
                            p_dtCongTo.Rows[0]["SoHienTai"] = createCounter;
                            Global.DataFactory.RunSaveData(p_dtCongTo, query_congto);
                            Global.DataFactory.RunSaveData(p_dtQuocTich, query_quoctich);
                            scope.Complete();
                            scope.Dispose();
                        }
                    }

                }
                else
                {
                    using (TransactionScope scope = new TransactionScope())
                    {
                        Global.DataFactory.RunSaveData(p_dtQuocTich, query_quoctich);
                        scope.Complete();
                        scope.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                StringBuilder strSQL = new StringBuilder();
                strSQL.Append("insert into HT_NhatKyLoi(ManHinh,SoCT,NgayPhatSinh,UserName,MaLoi,DienGiai,TenHam) values(@ManHinh,@SoCT,@NgayPhatSinh,@UserName,@MaLoi,@DienGiai,@TenHam)");
                SQLServer.Param[] Params = new SQLServer.Param[7];
                Params[0] = new SQLServer.Param("@ManHinh", "frmQuocTich");
                Params[1] = new SQLServer.Param("@SoCT", p_dtQuocTich.Rows[0]["MaQuocTich"]);
                Params[2] = new SQLServer.Param("@NgayPhatSinh", LibraryFormatConverter.ConvertStringDDMMYYYYToYYYYMMDD(LibraryFormatConverter.SetStringDateDefault));
                Params[3] = new SQLServer.Param("@UserName", Global.UserName);
                Params[4] = new SQLServer.Param("@MaLoi", "");
                Params[5] = new SQLServer.Param("@DienGiai", ex.Message);
                Params[6] = new SQLServer.Param("@TenHam", "InsertDanhMucQuocTich");
                Global.DataFactory.NonQuery(strSQL.ToString(), Params);
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Cập nhật danh mục quốc tịch
        /// </summary>
        /// <param name="dt"></param>
        public void fg_UpdateDanhMucQuocTich(DataTable dt)
        {
            try
            {
                string query_QuocTich = "Select * from HT_DM_QuocTich where 1 = 2";
                using (TransactionScope scope = new TransactionScope())
                {
                    Global.DataFactory.RunSaveData(dt, query_QuocTich);
                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                throw new System.Configuration.Provider.ProviderException(ex.Message);
            }
        }
        /// <summary>
        /// User: TAMNN 31/07/2014
        /// Mục đích: lấy danh sách theo danh mục
        /// </summary>
        /// <returns></returns>
        public DataTable ft_GetListdata(IList<string> list, string Orderby)
        {
            string str = string.Join(",", list.ToArray());
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT ");
            strSQL.Append(str);
            strSQL.Append(" from HT_DM_QuocTich with(nolock)");
            strSQL.Append(!string.IsNullOrEmpty(Orderby) ? (" order by " + Orderby) : "");
            return Global.DataFactory.GetDataTable(strSQL.ToString());
        }
        /// <summary>
        /// User:TAMN 31/07/2014
        /// Lay counter quốc tịch
        /// </summary>
        /// <param name="MaTienTo"></param>
        /// <returns></returns>
        public string fs_LayCongToTuDong(string MaTienTo)
        {
            return _BizCommon.fs_GetCodeId(MaTienTo);
        }

        /// <summary>
        /// User:TAMNN 28/07/2014 
        /// Mục đích: kiểm tra mã quốc tịch đã tồn tại hay chưa
        /// </summary>
        /// <param name="p_sTableName"></param>
        /// <param name="p_sfill"></param>
        /// <param name="p_sGiatri"></param>
        /// <param name="p_sDieuKien"></param>
        /// <returns></returns>
        public string fs_CheckDataExistsQuocTich(string p_sGiatri)
        {
            return _BizCommon.fs_LayDuLieuTrongTable("HT_DM_QuocTich", "MaQuocTich", "MaQuocTich  = N'" + p_sGiatri +"'");
        }



    }
}
